﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
'---------数据库
Public Class DBOperation
    'Public Connstr As String = "Data Source=60.18.250.78,51923;Initial Catalog=TLHX_OA;User ID=tlhxoa;Password=fz*TP_ym;"
    'Public Connstr As String = "Data Source=192.168.1.238;Initial Catalog=TLHX_OA;User ID=tlhxoa;Password=fz*TP_ym;"
    Public Connstr As String = "Data Source=LOCALHOST;Initial Catalog=BillManage;User ID=sa;Password=1;"
    'Private BInfo As New BaseType.BaseType.Conn_Str
    'Private DControl As New DBOperation
#Region "打开数据库连接"
    Public Function OpenDB(ByRef Tconn As SqlConnection, Optional ByRef ErrString As String = "") As Boolean
        Try
            If Tconn Is Nothing Then
                Tconn = New SqlConnection(Connstr)
            End If
            Tconn.Open()
            Return True
        Catch ex As Exception
            ErrString = String.Format("{0}{1}ERR Code:3", ex.Message, vbCrLf)
            Return False
        End Try
        Return False
    End Function
#End Region
#Region "执行SQL查询"
    Public Function QuerySQL(ByVal sqlstr As String, ByRef ds As DataSet, Optional ByRef ErrString As String = "") As Boolean
        If Trim(sqlstr) = String.Empty Then
            ErrString = String.Format("错误的参数!{0}ERR Code:1", vbCrLf)
            Return False
        End If
        ds = New DataSet
        Dim Tconn As New SqlConnection(Connstr)
        If OpenDB(Tconn, ErrString) = False Then
            Return False
        Else
            Try
                Dim da As New SqlDataAdapter(sqlstr, Tconn)
                da.Fill(ds)
                If ds.Tables.Count < 1 Then
                    ErrString = "内部服务器错误"
                    Return False
                End If
                Tconn.Close()
                Tconn.Dispose()
                Return True
            Catch ex As Exception
                Tconn.Dispose()
                ErrString = String.Format("{0}{1}ERR Code:4", ex.Message, vbCrLf)
                Return False
            End Try
        End If
    End Function
#End Region
#Region "执行SQL命令"
    Public Function ExecSQL(ByVal sqlstr As String, Optional ByRef ErrString As String = "") As Boolean
        If Trim(sqlstr) = String.Empty Then
            ErrString = String.Format("错误的参数!{0}ERR Code:1", vbCrLf)
            Return False
        End If
        Dim tconn As New SqlConnection(Connstr)
        If OpenDB(tconn, ErrString) = False Then
            tconn = Nothing
            Return False
        End If
        Dim cmd As New SqlCommand(sqlstr, tconn)
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            cmd = Nothing
            tconn.Close()
            tconn.Dispose()
            Return True
        Catch ex As Exception
            tconn.Dispose()
            ErrString = String.Format("{0}{1}ERR Code:4", ex.Message, vbCrLf)
            Return False
        End Try
    End Function
#End Region
#Region "获取单据号"
    Public Function GetBillId(ByVal FBcode As String, ByVal Ftab As String, ByRef Bid As String, Optional ByRef Errstring As String = "") As Boolean
        Dim ds As DataSet = Nothing
        Dim sqlstr As String = String.Format("select id from {0} where FBcode='{1}'", Ftab, FBcode)
        If QuerySQL(sqlstr, ds, Errstring) = False Then
            Return False
        End If
        If ds.Tables.Count < 1 Then
            Errstring = "未知错误!"
            Return False
        End If
        If ds.Tables(0).Rows.Count < 1 Then
            Errstring = "未找到单据"
            Return False
        End If
        Bid = ds.Tables(0).Rows(0).Item("id").ToString
        Return True

    End Function
#End Region
#Region "检查权限"
    Public Function CheckAuth(ByVal Uid As String, ByVal FCate As String, ByVal FKey As String) As Boolean
        Try
            If Uid.Trim = String.Empty Or FCate.Trim = String.Empty Or FKey.Trim = String.Empty Then
                Return False
            End If
            If Uid = 1 Then
                Return True
            End If
            Dim ds As New DataSet
            Dim sqlstr As String = String.Format("select id from t_AuthV where FGroup='{0}' and FKey='{1}'", FCate.Trim, FKey.Trim)
            Dim sid As String = String.Empty
            If QuerySQL(sqlstr, ds) = False Then
                Return False
            End If
            If ds.Tables.Count < 1 Then
                Return False
            End If
            If ds.Tables(0).Rows.Count < 1 Then
                Return False
            End If
            sid = ds.Tables(0).Rows(0).Item("id")
            ds = New DataSet
            If sid.Trim = String.Empty Or CInt(sid.Trim) < 1 Then
                Return False
            End If
            '--查询是否有权限
            sqlstr = String.Format("select count(*) as a from t_Auth where FUid={0} and FAid={1}", Uid, sid)
            If QuerySQL(sqlstr, ds) = False Then
                Return False
            End If
            If ds.Tables.Count < 1 Then
                Return False
            End If
            If ds.Tables(0).Rows.Count < 1 Then
                Return False
            End If
            If ds.Tables(0).Rows(0).Item("a") > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        End Try
    End Function
#End Region
#Region "通过SESSION获取用户信息"
    Public Function _GetUserBySession(ByVal USession As String, ByRef ErrString As String) As Boolean
        Try
            If USession.Trim = String.Empty Then
                ErrString = "登录码不能为空!"
                Return False
            End If
            ExecSQL("update t_user set FSession='',FBreathTime=NULL where FBreathTime<'" & Now.AddMinutes(-3) & "'")
            Dim ds As New DataSet
            If QuerySQL(String.Format("select count(*) as a from t_user where FSession='{0}'", USession), ds, ErrString) = False Then
                ErrString = String.Format("验证用户失败!{0}错误信息:{1}", vbCrLf, ErrString)
                Return False
            End If
            If ds.Tables.Count < 1 Then
                ErrString = String.Format("验证用户失败!{0}错误信息:内部服务器错误!", vbCrLf)
                Return False
            End If
            If ds.Tables(0).Rows(0).Item("a") < 1 Then
                ErrString = "验证用户失败!"
                Return False
            End If
            Return True
        Catch ex As Exception
            ErrString = ex.Message
            Return False
        End Try
    End Function
#End Region
#Region "写操作记录"
    Public Function WriteSysLog(ByVal FGroup As String, ByVal FUid As Integer, ByVal FUName As String, ByVal FSubject As String, ByVal FContent As String, ByVal FSeverity As Integer) As Boolean
        Try
            Dim sqlstr As String = String.Format("insert into t_Logs(FGroup,FUid,FSubject,FContent,FDate,FUName,FSeverity) values('{3}','{0}','作废单据','用户ID:{0} 用户名:{1} 操作:{2}',Getdate(),'{1}',{3})", FUid, FUName, FContent, FSeverity)
            Return ExecSQL(sqlstr)
        Catch ex As Exception
            Return False
        End Try
    End Function
#End Region
End Class


